/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */


import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Enumeration;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 *
 * @author Bor
 *
 * insert new datastring in to db
 *
 * pgsample INSERT INTO t_first(sdescr, descr, createtime, moditime) VALUES *
 * ('sdescr1', 'descr1', now(), now()); select * from t_first;
 */
@WebServlet(name = "create", urlPatterns = {"/create"})
public class create extends HttpServlet {

    String connectionURL = "jdbc:postgresql://192.168.1.89:5432/postgis";
    Connection connection = null;
    String username = "pgsql";
    String pass = "pgsql";

    /**
     * Processes requests for both HTTP
     * <code>GET</code> and
     * <code>POST</code> methods.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();

        //get Time
        Calendar now = Calendar.getInstance();
        String outputString = ("_[" + now.getTimeInMillis() + "|" + new java.util.Date()) + "]";

        try {
            connectionURL = getServletConfig().getInitParameter("pgcon");
            username = getServletConfig().getInitParameter("pgusr");
            pass = getServletConfig().getInitParameter("pgpwd");
        } finally {
        }

        //get external Params
        Enumeration en = request.getParameterNames();
        while (en.hasMoreElements()) {
            String paramName = (String) en.nextElement();
            out.println(paramName + " = " + request.getParameter(paramName) + "<br/>");
            outputString += "[" + paramName + "=" + request.getParameter(paramName) + "]";
        }
        //debug
        out.println("<hr/>" + outputString + "<hr/>");//+pos[1]);


        try {
            /* TODO output your page here. You may use following sample code. */

            //adr = new String(request.getParameter("adr").getBytes("ISO-8859-1"), "utf-8").toUpperCase();
            //getParam
            String param1 = new String(request.getParameter("param1").getBytes("ISO-8859-1"), "utf-8").toUpperCase();

            String param2 =  new String(request.getParameter("param2").getBytes("ISO-8859-1"), "utf-8").toUpperCase();

            out.println("<html>");
            out.println("<head>");
            out.println("<title>Servlet create</title>");
            out.println("</head>");
            out.println("<body>");
            out.println("<h1>Servlet create at " + request.getContextPath() + "</h1>");

            out.println("<h1>param1 =  " + param1 + "</h1>");
            out.println("<h1>param2 =  " + param2 + "</h1>");
            //Math.round(Math.random() * 100)
            out.println(insertData("INSERT INTO t_first(sdescr, descr, createtime, moditime) VALUES  ('" + param1 + "', '" + param2 + "', now(), now());"));//insert Data
            out.println(selectData("select * from t_first order by id desc;", 5));//insert Data

            out.println("</body>");
            out.println("</html>");
        } finally {
            out.close();
        }
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP
     * <code>GET</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Handles the HTTP
     * <code>POST</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>

    public String insertData(String insstring) {
        String result = " string resilt - inited ";
        try {
// Load the database driver
            Class.forName("org.postgresql.Driver");
// Get a Connection to the database
            connection = DriverManager.getConnection(connectionURL, username, pass);
//Add the data into the database
//String sql = "insert into sample values(?,?)";

            PreparedStatement pst = connection.prepareStatement(insstring);

            //pst.setString(1, username);
            //pst.setString(2, pass);

            int numRowsChanged = pst.executeUpdate();
            if (numRowsChanged != 0) {
                result = "<br>Record has been inserted";
            } else {
                result = "failed to insert the data";
            }
            pst.close();
        } catch (ClassNotFoundException e) {
            result = "Couldn't load database driver: " + e.getMessage();
        } catch (SQLException e) {
            result = "SQLException caught: " + e.getMessage();
        } catch (Exception e) {
            result = e + ".";
        } finally {
// Always close the database connection.
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException ignored) {
                result = ignored + ".";
            }
        }
        return result + ":" + connectionURL + "." + username + "." + pass;
    }

    public String selectData(String selstring, int colcount) {
        String result = " string resilt - inited ";

        try {
// Load the database driver
            Class.forName("org.postgresql.Driver");
// Get a Connection to the database
            connection = DriverManager.getConnection(connectionURL, username, pass);
//Add the data into the database
//String sql = "insert into sample values(?,?)";

            Statement st = connection.createStatement();
            ResultSet rs = st.executeQuery(selstring);

//get request     
            //SELECT id, sdescr, descr, createtime, moditime FROM t_first;

            result = "<table >";
            while (rs.next()) {
//                result += "<br/>";
                result += "<tr>";
                for (int i = 1; i < colcount + 1; i++) {
                    result += "<td>" + rs.getString(i) + "</td>";
                }
                result += "</tr>";
            }
            result += "</table>";
//            result += "<hr/>";
//\get request            


            rs.close();
            st.close();
        } catch (ClassNotFoundException e) {
            result = "Couldn't load database driver: " + e.getMessage();
        } catch (SQLException e) {
            result = "SQLException caught: " + e.getMessage();
        } catch (Exception e) {
            result = e + ".";
        } finally {
// Always close the database connection.
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException ignored) {
                result = ignored + ".";
            }
        }
        return result + ":" + connectionURL + "." + username + "." + pass;
    }
}
